package com.yanqu.road.tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class TableImport {

    private Connection conn;

    private String tableName;

    private ExcelData excelData;

    private List<Integer> fieldList;

    public TableImport(Connection conn, String tableName, ExcelData excelData) {
        this.conn = conn;
        this.tableName = tableName;
        this.excelData = excelData;
    }

    public void importData() {
        System.out.println("clear " + tableName);
        clear();
        String insertSql = createInsertSql();
        System.out.println(insertSql);
        int size = insert(insertSql);
        System.out.println("import " + tableName + " success. data size: " + size);
    }

    private String createInsertSql() {
        Map<Integer, ExcelHead> headMap = excelData.getHeadMap();
        Map<Integer, ConfigDesc> descMap = excelData.getDescMap();
        for (Map.Entry<Integer, ConfigDesc> entry : descMap.entrySet()) {
            Integer key = entry.getKey();
            ConfigDesc desc = entry.getValue();
            if (desc.isClient()) {
                headMap.remove(key);
            }
        }
        fieldList = new ArrayList<>(headMap.keySet());
        StringBuilder sqlBuilder = new StringBuilder("insert into  ").append(tableName).append("  (");
        for (Integer key : fieldList) {
            ExcelHead head = headMap.get(key);
            sqlBuilder.append("`").append(head.getField()).append("`").append(",");
        }
        sqlBuilder.deleteCharAt(sqlBuilder.length() - 1).append(")");
        sqlBuilder.append(" values (");
        for (int i = 0; i < fieldList.size(); i++) {
            sqlBuilder.append("?,");
        }
        sqlBuilder.deleteCharAt(sqlBuilder.length() - 1).append(")");

        return sqlBuilder.toString();
    }

    private void clear() {
        String clearSql = "delete from " + tableName;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(clearSql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            ps.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        try {
            ps.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private int  insert(String sql) {
        PreparedStatement ps = null;
        int size = 0;
        try {
            List<Map<Object, Object>> dataList = excelData.getDataList();
            Map<Integer, ExcelHead> headMap = excelData.getHeadMap();
            int maxNum = 200;
            int num = 0;
            ps = conn.prepareStatement(sql);
            for (Map<Object, Object> objectMap : dataList) {
                for (int i = 0; i < fieldList.size(); i++) {
                    Integer key = fieldList.get(i);
                    ExcelHead head = headMap.get(key);
                    String type = head.getType();
                    Object value = objectMap.get(key);
                    ps.setObject(i + 1, parseValue(value,type));
                }
                ps.addBatch();
                num++;
                size++;
               /* if (num >= maxNum) {
                    ps.executeBatch();
                    ps.clearBatch();
                    num = 0;
                }*/
            }
            if (num > 0) {
                ps.executeBatch();
                conn.commit();
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return size;
    }

    private Object parseValue(Object value, String type) {
    /*    if (Objects.equals("int", type)) {
            return Integer.parseInt(value.toString());
        }
        if (Objects.equals("long", type)) {
            return Integer.parseInt(value.toString());
        }*/
        if(Objects.equals("string", type)){
            String tempValue = value.toString();
            tempValue = tempValue.replace("_x000D_", "\r");
            value = tempValue;
        }
        return value;
    }
}
